import datetime
import lib.mysql as mysql
import lib.config as config
import lib.util
import lib.util as util


# 小程序补贴
def getWechat():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d') AS stats_date, IFNULL(SUM(`amount`), 0) AS had_used_value, COUNT(*) AS had_used_num from customer_income where created_at between '{}' and '{}' group by stats_date".format(start_time, end_time)
    wechat_list = db.getRows(sql)
    wechat_map = {a[0]: {'had_used_value': a[1], 'had_used_num': a[2]} for a in wechat_list}
    date_list = util.get_full_range_date(start_time, end_time)
    insert_data = []
    for date in date_list:
        item = {
            'stats_date': date,
            'had_used_num': util.get_value_by_map(wechat_map, f'{date}.had_used_num'),
            'had_used_value': util.get_value_by_map(wechat_map, f'{date}.had_used_value'),
        }
        insert_data.append(item)
    return insert_data


def getRecommendationNum():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d') AS `stats_date`, COUNT(*) AS `total_num` from recommendation where promote_channel = 1 and created_at between '{}' and '{}' group by stats_date".format(start_time, end_time)
    return db.getRows(sql)


def getRecommendationAmount():
    sql = "select DATE_FORMAT(coupon.created_at, '%Y-%m-%d') AS `stats_date`, IFNULL(SUM(`discount_digit`), 0) AS `total_value` from coupon left join customer_sale on coupon.sale_id = customer_sale.id where customer_sale.situation = 1 and customer_sale.category = 1 and coupon.discount_type = 0 and coupon.created_at between '{}' and '{}' group by stats_date".format(start_time, end_time)
    digit_list = db.getRows(sql)
    digit_map = {a[0]: a[1] for a in digit_list}
    sql = "select DATE_FORMAT(coupon.created_at, '%Y-%m-%d') AS `stats_date`, IFNULL(SUM(`actual_discount`), 0) AS `total_value` from coupon left join customer_sale on coupon.sale_id = customer_sale.id where customer_sale.situation = 1 and customer_sale.category = 1 and coupon.discount_type = 1 and coupon.created_at between '{}' and '{}' group by stats_date".format(start_time, end_time)
    limit_list = db.getRows(sql)
    limit_map = {a[0]: a[1] for a in limit_list}
    date_list = util.get_full_range_date(start_time, end_time)
    insert_data = {}
    for date in date_list:
        insert_data[date] = digit_map.get(date, 0) + limit_map.get(date, 0)
    return insert_data


# 乘客补贴
def getMainDataForProportion():
    sql = "select DATE_FORMAT(ride_order.last_pay_time, '%Y-%m-%d') AS stats_date,IFNULL(SUM(ride_transaction_log.coupon_paid), 0) AS coupon_amount, IFNULL(SUM(ride_transaction_log.allowance_amount), 0) AS allowance_amount, IFNULL(SUM(ride_transaction_log.balance_paid), 0) AS balance_amount from ride_transaction_log left join ride_order on ride_transaction_log.ride_order_id = ride_order.id where ride_order.state = 2 and ride_order.refunded = 0 and ride_order.last_pay_time between '{}' and '{}' group by stats_date".format(start_time, end_time)
    mulit_list = db.getRows(sql)
    mulit_map = {a[0]: {'coupon_amount': a[1], 'allowance_amount': a[2], 'balance_amount': a[3]} for a in mulit_list}
    wechat_list = getWechat()
    wechat_map = {a['stats_date']: a['had_used_value'] for a in wechat_list}
    ride_order_list = getRideOrderCount()
    ride_order_map = {a[0]: a[1] for a in ride_order_list}
    recommendation_num_list = getRecommendationNum()
    recommendation_num_map = {a[0]: a[1] for a in recommendation_num_list}
    recommendation_amount_map = getRecommendationAmount()
    date_list = util.get_full_range_date(start_time, end_time)
    insert_data = []
    for date in date_list:
        item = {
            'stats_date': date,
            'coupon_amount': util.get_value_by_map(mulit_map, f'{date}.coupon_amount'),
            'allowance_amount': util.get_value_by_map(mulit_map, f'{date}.allowance_amount'),
            'balance_amount': util.get_value_by_map(mulit_map, f'{date}.balance_amount'),
            'wechat_amount': wechat_map.get(date, 0),
            'order_num': ride_order_map.get(date, 0),
            'recommendation_num': recommendation_num_map.get(date, 0),
            'recommendation_amount': recommendation_amount_map.get(date, 0)
        }
        insert_data.append(item)
    return insert_data


# rideOrder订单数
def getRideOrderCount():
    sql = "select DATE_FORMAT(last_pay_time, '%Y-%m-%d') AS `stats_date`, COUNT(*) AS `total_num` from ride_order where state = 2 and refunded = 0 and last_pay_time between '{}' and '{}' group by stats_date".format(start_time, end_time)
    return db.getRows(sql)


# 优惠券 getMainDataForCoupon
def getMainDataForCoupon():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d') AS `stats_date`, IFNULL(SUM(`discount_digit`), 0) AS `total_value`, COUNT(*) AS `total_num` from coupon where discount_type = 0 and created_at BETWEEN '{}' and '{}' group by stats_date".format(start_time, end_time)
    digit_list = db.getRows(sql)
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d') AS `stats_date`, IFNULL(SUM(`discount_digit`), 0) AS `total_value`, COUNT(*) AS `total_num` from coupon where discount_type = 1 and created_at BETWEEN '{}' and '{}' group by stats_date".format(start_time, end_time)
    limit_list = db.getRows(sql)
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d') AS `stats_date`, IFNULL(SUM(`actual_discount`), 0) AS `total_value`, COUNT(*) AS `total_num`, COUNT(DISTINCT `user_id`) AS `total_user` from coupon where had_used = 1 and created_at between '{}' and '{}' group by stats_date".format(start_time, end_time)
    used_list = db.getRows(sql)
    ride_order = getRideOrderCount()
    date_list = lib.util.get_full_range_date(start_time, end_time)
    digit_map = {a[0]: {'value': a[1], 'num': a[2]} for a in digit_list}
    limit_map = {a[0]: {'value': a[1], 'num': a[2]} for a in limit_list}
    used_map = {a[0]: {'total_value': a[1], 'total_num': a[2], 'total_user': a[3]} for a in used_list}
    ride_order_map = {a[0]: a[1] for a in ride_order}
    insert_data = []
    for date in date_list:
        item = {
            'stats_date': date,
            'user_num': util.get_value_by_map(used_map, f'{date}.total_user'),
            'had_used_value': util.get_value_by_map(used_map, f'{date}.total_value'),
            'had_used_num': util.get_value_by_map(used_map, f'{date}.total_num'),
            'total_value': util.get_value_by_map(digit_map, f'{date}.value') + util.get_value_by_map(limit_map, f'{date}.value'),
            'total_num': util.get_value_by_map(digit_map, f'{date}.num') + util.get_value_by_map(limit_map, f'{date}.num'),
            'order_num': ride_order_map.get(date, 0),
        }
        insert_data.append(item)
    return insert_data


# 波浪补贴 getMainDataForWave
def getMainDataForWave():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d') AS stats_date, IFNULL(SUM(`nominal_allowance_amount`), 0) AS `nominal_amount`, IFNULL(SUM(`actual_allowance_amount`), 0) AS `actual_amount`, COUNT(*) AS `order_num` from activity_wave_allowance_log where created_at between '{}' and '{}' group by stats_date".format(start_time, end_time)
    wave_list = db.getRows(sql)
    wave_map = {a[0]: {'nominal_amount': a[1], 'actual_amount': a[2], 'order_num': a[3]} for a in wave_list}
    date_list = util.get_full_range_date(start_time, end_time)
    insert_data = []
    for date in date_list:
        item = {
            'stats_date': date,
            'order_num': util.get_value_by_map(wave_map, f'{date}.order_num'),
            'nominal_amount': util.get_value_by_map(wave_map, f'{date}.nominal_amount'),
            'actual_amount': util.get_value_by_map(wave_map, f'{date}.actual_amount')
        }
        insert_data.append(item)
    return insert_data


# 余额 getMainDataForBalance
def getMainDataForBalance():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d') AS `stats_date`, IFNULL(SUM(`donation_amount`), 0) AS `total_value` from recharge where operation_mode = 0 and state = 2 and refunded = 0 and created_at between '{}' and '{}' group by stats_date".format(start_time, end_time)
    add_list = db.getRows(sql)
    add_map = {a[0]: a[1] for a in add_list}
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d') AS `stats_date`, IFNULL(SUM(`donation_amount`), 0) AS `total_value`, COUNT(DISTINCT customer_id) AS `total_user` from recharge where operation_mode = 1 and state = 2 and refunded = 0 and created_at between '{}' and '{}' group by stats_date".format(start_time, end_time)
    reduct_list = db.getRows(sql)
    reduct_map = {a[0]: {'total_value': a[1], 'total_user': a[2]} for a in reduct_list}
    date_list = util.get_full_range_date(start_time, end_time)
    insert_data = []
    for date in date_list:
        item = {
            'stats_date': date,
            'total_value': add_map.get(date, 0),
            'had_used_value': util.get_value_by_map(reduct_map, f'{date}.total_value'),
            'user_num': util.get_value_by_map(reduct_map, f'{date}.total_user'),
        }
        insert_data.append(item)
    return insert_data


def handel_coupon_list():
    coupon_list = getMainDataForCoupon()
    call_size = len(coupon_list)
    util.add_log(f"数据共 {call_size}")
    db1.cursor.execute("START TRANSACTION")
    try:
        sql = "delete from cache_allowance_customer_coupon where (stats_date BETWEEN '{}' AND '{}')".format(start_time, end_time)
        db1.cursor.execute(sql)
        if call_size > 0:
            sql = "INSERT INTO `cache_allowance_customer_coupon` (`stats_date`, `total_value`, `total_num`, `had_used_value`, `had_used_num`, `order_num`, `user_num`,`created_at`, `updated_at`) VALUES "
            for item in coupon_list:
                sql += f"('{item['stats_date']}', {item['total_value']}, {item['total_num']}, {item['had_used_value']}, {item['had_used_num']}, {item['order_num']}, {item['user_num']}, '{current_time}', '{current_time}'),"
            sql = sql.strip(',')
            db1.cursor.execute(sql)
        db1.conn.commit()
        util.add_log("处理完成")
    except Exception as e:
        db1.conn.rollback()
        util.add_log(f"处理失败: {e}")


def handel_balance_list():
    coupon_list = getMainDataForBalance()
    call_size = len(coupon_list)
    util.add_log(f"数据共 {call_size}")
    db1.cursor.execute("START TRANSACTION")
    try:
        sql = "delete from cache_allowance_customer_balance where (stats_date BETWEEN '{}' AND '{}')".format(start_time, end_time)
        db1.cursor.execute(sql)
        if call_size > 0:
            sql = "INSERT INTO `cache_allowance_customer_balance` (`stats_date`, `total_value`, `had_used_value`, `user_num`, `created_at`, `updated_at`) VALUES "
            for item in coupon_list:
                sql += f"('{item['stats_date']}', {item['total_value']}, {item['had_used_value']}, {item['user_num']}, '{current_time}', '{current_time}'),"
            sql = sql.strip(',')
            db1.cursor.execute(sql)
        db1.conn.commit()
        util.add_log("处理完成")
    except Exception as e:
        db1.conn.rollback()
        util.add_log(f"处理失败: {e}")


def handel_proportion_list():
    coupon_list = getMainDataForProportion()
    call_size = len(coupon_list)
    util.add_log(f"数据共 {call_size}")
    db1.cursor.execute("START TRANSACTION")
    try:
        sql = "delete from cache_allowance_customer_proportion where (stats_date BETWEEN '{}' AND '{}')".format(start_time, end_time)
        db1.cursor.execute(sql)
        if call_size > 0:
            sql = "INSERT INTO `cache_allowance_customer_proportion` (`stats_date`, `coupon_amount`, `allowance_amount`, `balance_amount`, `wechat_amount`, `order_num`, `recommendation_num`, `recommendation_amount`, `created_at`, `updated_at`) VALUES "
            for item in coupon_list:
                sql += f"('{item['stats_date']}', {item['coupon_amount']}, {item['allowance_amount']}, {item['balance_amount']}, {item['wechat_amount']}, {item['order_num']}, {item['recommendation_num']}, {item['recommendation_amount']}, '{current_time}', '{current_time}'),"
            sql = sql.strip(',')
            db1.cursor.execute(sql)
        db1.conn.commit()
        util.add_log("处理完成")
    except Exception as e:
        db1.conn.rollback()
        util.add_log(f"处理失败: {e}")


def handel_wave_list():
    coupon_list = getMainDataForWave()
    call_size = len(coupon_list)
    util.add_log(f"数据共 {call_size}")
    db1.cursor.execute("START TRANSACTION")
    try:
        sql = "delete from cache_allowance_customer_wave where (stats_date BETWEEN '{}' AND '{}')".format(start_time, end_time)
        db1.cursor.execute(sql)
        if call_size > 0:
            sql = "INSERT INTO `cache_allowance_customer_wave` (`stats_date`, `order_num`, `nominal_amount`, `actual_amount`, `created_at`, `updated_at`) VALUES "
            for item in coupon_list:
                sql += f"('{item['stats_date']}', {item['order_num']}, {item['nominal_amount']}, {item['actual_amount']}, '{current_time}', '{current_time}'),"
            sql = sql.strip(',')
            db1.cursor.execute(sql)
        db1.conn.commit()
        util.add_log("处理完成")
    except Exception as e:
        db1.conn.rollback()
        util.add_log(f"处理失败: {e}")


def handel_wechat_list():
    coupon_list = getWechat()
    call_size = len(coupon_list)
    util.add_log(f"数据共 {call_size}")
    db1.cursor.execute("START TRANSACTION")
    try:
        sql = "delete from cache_allowance_customer_wechat where (stats_date BETWEEN '{}' AND '{}')".format(start_time, end_time)
        db1.cursor.execute(sql)
        if call_size > 0:
            sql = "INSERT INTO `cache_allowance_customer_wechat` (`stats_date`, `had_used_num`, `had_used_value`, `created_at`, `updated_at`) VALUES "
            for item in coupon_list:
                sql += f"('{item['stats_date']}', {item['had_used_num']}, {item['had_used_value']}, '{current_time}', '{current_time}'),"
            sql = sql.strip(',')
            db1.cursor.execute(sql)
        db1.conn.commit()
        util.add_log("处理完成")
    except Exception as e:
        db1.conn.rollback()
        util.add_log(f"处理失败: {e}")


start_time = util.get_start_date()
end_time = util.get_end_date()

current_time = datetime.datetime.now()
if end_time is None:
    # 转为指定格式
    end_time = current_time.strftime("%Y-%m-%d 23:59:59")
else:
    end_time = f"{end_time} 23:59:59"

if start_time is None:
    start_time = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S") - datetime.timedelta(days=3)
    start_time = start_time.strftime("%Y-%m-%d")
current_time = current_time.strftime("%Y-%m-%d %H:%M:%S")

util.add_log(f"即将处理 {start_time} - {end_time} 的乘客补贴数据")

db = mysql.Db(config.config['mysql']['master'])
db1 = mysql.Db(config.config['mysql']['slave'])

util.add_log("正在处理优惠券数据")
handel_coupon_list()
util.add_log("正在处理余额")
handel_balance_list()
util.add_log("正在处理补贴")
handel_proportion_list()
util.add_log("handle wave")
handel_wave_list()
util.add_log("handel_wechat_list")
handel_wechat_list()
